/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mb.webserver.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import mb.config.ConstantsMsg;
import mb.mbmiddle.MbPartMiddle;
import mb.mbpart.memory.QueryMemory;
import mb.mbpart.object.ATMObject;
import mb.mbpart.object.BranchObject;
import mb.mbpart.object.NewsObject;
import mb.mbpart.object.StateObject;
import mb.utils.StringUtils;

/**
 *
 * @author thanh.nguyenchi
 */
public class SQLServerConnection {
    
    Connection conn = null; 
    String url;
    String user;
    String pass;
    int status;
    
    public SQLServerConnection(String url,String user, String pass){
        this.url = url;
        this.user = user;
        this.pass = pass;
        
        initConnection();
    }
    
    public SQLServerConnection(){
        this.url = MbPartMiddle.objDbConfig.getUrl();
        this.user = MbPartMiddle.objDbConfig.getUser();
        this.pass = MbPartMiddle.objDbConfig.getPass();
        
        initConnection();
    }
    
    public int getStatus(){
        return status;
    }
    
    private void initConnection(){
        try{            
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            conn = DriverManager.getConnection(url,user,pass);
            if (conn != null){
                status = 1;
            }
            else{
                status = 0;
            }
        }
        catch(SQLException ex){
            status = 0;
        }
        catch(Exception ex) { 
            status = 0;
        } 
    }
    
    private void closeConnection(){
        if (conn != null){
            try{
                conn.close();
            }
            catch(Exception ex){
                
            }            
        }
    }
    
    public String getTopATM(double lat,double lng, String sessionId){
        Statement stmt = null;
        
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spQueryTopATM] \n" +
                        "       @LAT = " + lat + ",\n" +
                        "       @LNG = " + lng + "\n" +
                        "SELECT	'Return Value' = @return_value ";                
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                ATMObject obj = new ATMObject();
                obj.C_NAME = rs.getString(1);
                obj.C_ADDRESS = rs.getString(2);
                obj.C_LATITUDE = rs.getDouble(3);
                obj.C_LONGITUDE = rs.getDouble(4);
                obj.C_HOTLINE = rs.getString(5);
                obj.C_HOMEPAGE = rs.getString(6);
                obj.C_ATM_IMAGE = rs.getString(7);
                obj.C_ATM_ID = rs.getInt(8);
                obj.C_STATUS = rs.getInt(9);
                sResult = sResult + obj.toJson() + ",";                     
            }

            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";

            // update to memory
            QueryMemory objQueryMem = new QueryMemory();
            objQueryMem.setQueryTime(new Date());
            objQueryMem.setData(sResult);
            // key
            String queryKey = sessionId + ConstantsMsg.STR_ATM_QUERY;
            MbPartMiddle.objQueryMemory.put(queryKey,objQueryMem);            
      
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception ex ) {
            return "[]";
        }            
        return sResult;
    }
    
    public String getTopBranch(double lat,double lng, String sessionId){
        Statement stmt = null;
        
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spQueryTopBranch] \n" +
                        "       @LAT = " + lat + ",\n" +
                        "       @LNG = " + lng + "\n" +
                        "SELECT	'Return Value' = @return_value ";                
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                BranchObject obj = new BranchObject();
                obj.C_NAME = rs.getString(1);
                obj.C_ADDRESS = rs.getString(2);
                obj.C_LATITUDE = rs.getDouble(3);
                obj.C_LONGITUDE = rs.getDouble(4);
                obj.C_FAX = rs.getString(5);
                obj.C_PHONE = rs.getString(6);
                obj.C_HOMEPAGE = rs.getString(7);
                obj.C_DESCRIPTION = rs.getString(8);
                obj.C_BRANCH_ID = rs.getInt(9);
                sResult = sResult + obj.toJson() + ",";                     
            }

            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";

            // update to memory
            QueryMemory objQueryMem = new QueryMemory();
            objQueryMem.setQueryTime(new Date());
            objQueryMem.setData(sResult);
            // key
            String queryKey = sessionId + ConstantsMsg.STR_BRA_QUERY;
            MbPartMiddle.objQueryMemory.put(queryKey,objQueryMem);   
            
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception ex ) {
            return "[]";
        }            
        return sResult;
    }
    
    public String getTopServices(double lat,double lng,long cateid, String sessionId){
        Statement stmt = null;
        
        String query =  "DECLARE	@return_value int \n" +
                        "EXEC	@return_value = [spQueryTopService] \n" +
                        "       @LAT = " + lat + ",\n" +
                        "       @LNG = " + lng + ",\n" +
                        "       @Cate_ID = " + cateid + "\n" +
                        "SELECT	'Return Value' = @return_value ";                
        
        String sResult = "[";  
        
        try {     
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);            
            while (rs.next()) {                
                NewsObject obj = new NewsObject();
                obj.C_NEWS_ID = rs.getInt(1);
                obj.C_TITLE = rs.getString(2);
                obj.C_TEASER = rs.getString(3);
                obj.C_CONTENT = rs.getString(4);
                obj.C_AVATAR = rs.getString(5);
                obj.C_ADDRESS = rs.getString(6);
                obj.C_LATITUDE = rs.getDouble(7);
                obj.C_LONGITUDE = rs.getDouble(8);
                obj.C_FROM_DATE = rs.getDate(9).toString();
                obj.C_TO_DATE = rs.getDate(10).toString();
                obj.C_PHONE = rs.getString(11);
                obj.C_LINK = rs.getString(12);
                obj.C_TYPE = cateid;   
                sResult = sResult + obj.toNewsJson() + ",";
            }

            if (sResult.endsWith(",")){
                sResult = sResult.substring(0, sResult.length() - 1);
            }
            
            sResult = sResult + "]";

            // update to memory
            QueryMemory objQueryMem = new QueryMemory();
            objQueryMem.setQueryTime(new Date());
            objQueryMem.setData(sResult);
            // key
            String queryKey = sessionId + cateid;
            MbPartMiddle.objQueryMemory.put(queryKey,objQueryMem);  
            
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception ex ) {
            return "[]";
        }            
        return sResult;
    }
}
